Combine Multiple Excel Files into one Excel File with VBA Code

Combine Multiple Excel files into one Excel File

The best and fastest way to merge Excel files is to use VBA macros. If you perform this task regularly, then VBA macro will come handy. Just Follow these steps :

Step 1

• Open a new workbook. We have opened one and named it Merge.
• We have put all the Excel files that we want to combine into a folder. For this tutorial, i have created a folder name Merge Excel in my C Drive and kept out Seven workbooks inside it.
• Then, you can press Alt+F11 to open the Visual Basic. A new Microsoft Visual Basic for Applications window will be displayed. 

• Then click Insert Menu - Module.

Step 2

• After that enter the following VBA code into the Module.

-------------------------------------------------------------------

Sub ConslidateWorkbooks()

Path = "\"

Filename = Dir(Path & "*.xls")

Do While Filename <> ""

Workbooks.Open Filename:=Path & Filename, ReadOnly:=True

For Each Sheet In ActiveWorkbook.Sheets

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Next Sheet

Workbooks(Filename).Close

Filename = Dir()

Loop

End Sub

-------------------------------------------------------------------

• Path ="in this area paste file save location link" 

Note : We have Used C:\...........\ as the the folder path because we have stored our Excel files inside the folder named Merge Excel which is located in C Drive. You will use the path to the folder you used to keep your excel files.

• Then click the Run Button or Press F5 to Execute the code.

You will now see all the 6 excel worksheets from the the Seven workbooks are merged into the merge.